import re
import time
import requests
import uuid
import datetime
from flask import make_response, request, send_from_directory
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial
import json
import logging
from flask_mail import Message

from redash import models, limiter, mail, settings
from redash.permissions import (
    require_default,
)
from redash.handlers import paymentactivity
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings
from redash.serializers import (
    serialize_data_result_to_xlsx,
)
import sys
import random
from typing import List


class PaymentNewResource(BaseResource):
    def get(self):
        result = models.Payment.get_id()
        return result[0]

    @require_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        payment_id=req["id"]
        otheramount = req["otheramount"] if len(str(req["otheramount"]))>0 else 0
        amount = req["amount"] if len(str(req["amount"]))>0 else 0
        customerid = req["customerid"] if len(str(req["customerid"]))>0 else 0
        employeeid = req["employeeid"] if len(str(req["employeeid"]))>0 else 0
        payname = req["payname"]
        code = req["code"]
        type = req["type"]
        if str(type)=="1" :
            try:
                message="收款单必须要有转款截图"
                attaches = models.Attaches.get_by_id(payment_id,"payment")
                ps = attaches.to_dict()
                annex = json.loads(json.dumps(ps['annex']))
                nums1=0  #转款截图
                for i in range(len(annex)):
                    if str(annex[i]["types"]) == "1":
                        nums1=nums1+1
                if nums1 ==0 :
                    message="收款单必须要有转款截图。"
                    abort(400, message="收款单必须要有转款截图。")
            except :
                abort(400, message=message)

        payment = models.Payment(
        id=payment_id,
        org=self.current_org,
        bill_date = req["bill_date"],
        type = type,
        customerid = customerid,
        employeeid= employeeid,
        code = code,
        name = req["name"] if "name" in req else "",
        amount = amount,
        othertype = req["othertype"] if "othertype" in req else "",
        otheramount = otheramount,
        payname = payname,
        accountname = req["accountname"] if "accountname" in req else "",
        accountcode = req["accountcode"] if "accountcode" in req else "",
        accountbank = req["accountbank"] if "accountbank" in req else "",
        paydate = req["paydate"] if "paydate" in req else None,
        message = req["message"] if "message" in req else "",
        signingtime = req["signingtime"] if "signingtime" in req else None,
        expirationtime = req["expirationtime"] if "expirationtime" in req else None,
        status = req["status"],
        userid = req["userid"],
        orgunitid = req["orgunitid"] if "orgunitid" in req else "0",
        )
        try:
            message="编号已存在."
            message= paymentactivity.PaymentActivityinsert_update.insert_update(self,req["paymentactivitydata"],payment_id)
            models.db.session.add(payment)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)


class PaymentResource(BaseResource):
    def get(self, payment_id):
        result = models.Payment.get_by_id(payment_id)
        return result.to_dict()

    @require_default
    def post(self, payment_id):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        otheramount = req["otheramount"] if len(str(req["otheramount"]))>0 else 0
        amount = req["amount"] if len(str(req["amount"]))>0 else 0
        payname = req["payname"]
        code = req["code"]
        customerid = req["customerid"] if len(str(req["customerid"]))>0 else 0
        employeeid = req["employeeid"] if len(str(req["employeeid"]))>0 else 0
        type = req["type"]
        if str(type)=="1" :
            try:
                message="收款单必须要有转款截图"
                attaches = models.Attaches.get_by_id(payment_id,"payment")
                ps = attaches.to_dict()
                annex = json.loads(json.dumps(ps['annex']))
                nums1=0  #转款截图
                for i in range(len(annex)):
                    if str(annex[i]["types"]) == "1":
                        nums1=nums1+1
                if nums1 ==0 :
                    message="收款单必须要有转款截图。"
                    abort(400, message="收款单必须要有转款截图。")
            except :
                abort(400, message=message)

        payment = models.Payment.get_by_id(payment_id)
        params = dict(bill_date = req["bill_date"], type = type,  customerid = customerid,  employeeid= employeeid,
            code = code, name = req["name"] if "name" in req else "", amount = amount, othertype = req["othertype"] if "othertype" in req else "", otheramount = otheramount,
        payname = payname,accountname = req["accountname"] if "accountname" in req else "", accountcode = req["accountcode"] if "accountcode" in req else "", 
        accountbank = req["accountbank"] if "accountbank" in req else "",
        paydate = req["paydate"] if "paydate" in req else None, message = req["message"] if "message" in req else "", 
        signingtime = req["signingtime"] if "signingtime" in req else None, expirationtime = req["expirationtime"] if "expirationtime" in req else None,       
        status = req["status"], userid = req["userid"], orgunitid = req["orgunitid"] if "orgunitid" in req else "0")
        try:
            message="编号已存在."
            message= paymentactivity.PaymentActivityinsert_update.insert_update(self,req["paymentactivitydata"],payment_id)
            self.update_model(payment, params)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message=message)
            abort(500)

class PaymentdeleteResource(BaseResource):
    @require_default
    def post(self, payment_id):
        payment =  models.Payment.get_by_id(payment_id)
        strsql="""delete From  attaches where sourcetype='payment' and org_id="""+str(self.current_org.id)+""" and sourceid="""+ payment_id+"""; 
            delete From paymentactivity where paymentid="""+ payment_id +"""; """
        models.db.session.execute(strsql)
        models.db.session.delete(payment)
        models.db.session.commit()

class PaymentListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        code = req["code"] if "code" in req else ""
        name = req["name"] if "name" in req else ""
        paytype = str(req["paytype"]) if "paytype" in req else "0"
        userid = str(req["userid"])
        status = str(req["status"]) if "status" in req else ""
        datadown = req["datadown"] if "datadown" in req else False
        orderfield = req["orderfield"] if "orderfield" in req else "code"
        order = req["order"] if "order" in req else 0
        if orderfield=="code" :
            orderfield ="t1.code"
        elif orderfield=="name":
            orderfield ="t1.name"
        else:
            orderfield ="t1.code"        

        strwhere ="t1.org_id=" +str(self.current_org.id) +" and t1.type="+ paytype
        if code !="" :
            strwhere = strwhere + " and t2.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t2.name like '%"+name+"%'"
        if status !='' :
            strwhere = strwhere + " and t1.status = '"+status+"'"

        if userid !='' :
            strwhere = strwhere + """ and  (t1.userid='""" + userid + """' or  t1.userid in (select  users.id from
                (with RECURSIVE T1 (keys,items) as
                (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                 from   (with RECURSIVE le (keys,items,parentkeys) as
                (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                 from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
                 union all
                 select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
                select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """') AA
                 union all
                 select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from T1)
                select * from T1
                union all
                (with RECURSIVE le (keys,items,parentkeys) as
                (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                 from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
                 union all
                 select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
                select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """'))T2
                inner join users on to_number((T2.items)->>'key', '9999999999999999999')=users.orgunit_id
                union all select users.id from public.users where users.orgunit_id in(select orgunit_id from public.users where id='""" + userid + """' )))"""
        strgetfield =""" t2.id,t2.paymentid,to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.customerid,t2.employeeid,t1.type,"""
        strgetfield =strgetfield +""" COALESCE(t10.strcustomername,'') as strcustomername,COALESCE(t10.representative,'') as representative,
                t1.code as pcode,t1.name as pname,t1.amount as totalamount,t1.othertype as totalothertype,
                t1.otheramount as totalotheramount,t1.payname,t1.accountname,t1.accountcode,t1.accountbank,
                to_char(t1.paydate,'YYYY-MM-DD') paydateformat,t1.message,
                COALESCE(to_char(t1.signingtime,'YYYY-MM-DD'),'') signingtimeformat,
                COALESCE(to_char(t1.expirationtime,'YYYY-MM-DD'),'') expirationtimeformat,t1.status,t1.userid,t1.orgunitid,
                t2.code,t2.name,t2.contractcode,t2.amount,t2.othertype,t2.otheramount,
                case when t1.status=0 then '草稿' when t1.status=1 then '审核中' else '已审核' end as strstatus,
                COALESCE(t3.name,'') as username,COALESCE(t4.name,'') as maintainername,COALESCE(t5.name,'') as signatoryname,
                COALESCE(t6.name,'') as recipientname,COALESCE(t7.name,'') as orgunitname"""

        tblname = """public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
            left join public.users t3 on t1.userid=t3.id
            left join public.users t4 on t2.maintainer=t4.id
            left join public.users t5 on t2.signatory=t5.id 
            left join public.users t6 on t2.recipient=t6.id
            left join public.orgunit t7 on t1.orgunitid=t7.id
            left join public.project t8 on t2.projectid=t8.id 
            left join public.projectdetail t9 on t2.projectid=t9.projectid and t2.projectdetailid=t9.id
            left join public.customers t10 on t1.customerid=t10.id """
        #orderfield ="t1.id"
        if not datadown :
            result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
            return result[0]
        else :
            params ="paymentoutlist" if paytype=="0" else "paymentinlist"
            queryshowfield = models.QueryShowField.get_by_code(params)
            ps = queryshowfield.to_dict()
            fieldsshow = ps['fieldsshow']
            fieldsall = json.loads(json.dumps(ps['fieldsall'])) 
            columns =[]
            strsql="select " + strgetfield + " from " + tblname +" where " +strwhere
            strgetfield = ""
            for i in range(len(fieldsall)):
                if fieldsall[i]["key"] in fieldsshow:
                    columns.append({ 'name' : fieldsall[i]["title"]})
                    strgetfield = strgetfield +","+  fieldsall[i]["key"] +" as "+ fieldsall[i]["title"]
                    
            orderfield = req["orderfield"] if "orderfield" in req else "code"
            strsql = "select " + strgetfield[1:] + " from (" + strsql + ") t1 order by "+orderfield
            if order==0 :
                strsql= strsql+" ASC "
            else :
                strsql= strsql+" DESC "
            strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
            data = models.Enums.get_selsql(strsql)
            
            article_info = {}
            da1 = json.loads(json.dumps(article_info))
            da1['columns'] = columns 
            da1['rows'] = data[0] 

            query_result = da1

            result = serialize_data_result_to_xlsx(query_result,"kuanxiang")
            return send_from_directory(result, "kuanxiang.xlsx",as_attachment=True)
        
class PaymentUnInListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        customername = req["customername"]if "customername" in req else ""
        representative = req["representative"]if "representative" in req else ""
        orgunitname = req["orgunitname"]if "orgunitname" in req else ""
        difference = str(req["difference"]) if "difference" in req else ""
        code = req["code"] if "code" in req else ""
        name = req["name"] if "name" in req else ""
        major = req["major"] if "major" in req else ""
        area = req["area"] if "area" in req else ""
        bytstatus= req["bytstatus"] if "bytstatus" in req else ""
        certificate = req["certificate"] if "certificate" in req else ""
        location = req["location"] if "location" in req else ""
        projectdetailid=str(req["projectdetailid"]) if "projectdetailid" in req else "0"

        orderfield = req["orderfield"] if "orderfield" in req else "code"
        order = req["order"] if "order" in req else 0
        if orderfield=="code" :
            orderfield ="t1.code"
        elif orderfield=="name":
            orderfield ="t1.name"
        elif orderfield=="major":
            orderfield ="t1.major"
        elif orderfield=="area":
            orderfield ="t1.area"
        elif orderfield=="bill_date":
            orderfield ="t1.bill_date"
        else:
            orderfield ="t1.code"        

        strwhere ="t1.org_id=" +str(self.current_org.id)+" and t1.status=2 "
        if code !="" :
            strwhere = strwhere + " and t2.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t2.name like '%"+name+"%'"
        if major !='' :
            strwhere = strwhere + " and  '"+major+"' = ANY(t1.major)"
        if area !='' :
            strwhere = strwhere + " and t1.area like '%"+area+"%'"
        if bytstatus !='' :
            strwhere = strwhere + " and t1.bytstatus like '%"+bytstatus+"%'"
        if certificate !='' :
            strwhere = strwhere + " and '"+certificate+"' = ANY(t2.certificate)"
        if location !='' :
            strwhere = strwhere + " and t2.location like '%"+location+"%'"
        if customername !='' :
            strwhere = strwhere + " and t4.strcustomername like '%"+customername+"%'"
        if orgunitname !='' :
            strwhere = strwhere + " and t6.name like '%"+orgunitname+"%'"
        if representative !='':
            strwhere = strwhere + " and t4.representative like '%"+representative+"%'"
        if difference =="1":
            strwhere = strwhere + " and (t2.amount-COALESCE(t7.amount,0)>0 or t2.id="""+projectdetailid+""")"""

        strgetfield =""" t2.id,t2.projectid,to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t2.employeeid,"""
        strgetfield =strgetfield +""" t1.code as needcode,t1.name as needname,t1.customerid,t1.neednum,t1.amount as totalamount,
            t1.schooled,array_to_string(t1.certificate,',') needcertificate,t1.location,
            case when t1.socialsecurity=1 then '有' else '无' end needsocialsecuritystr,
            t1.area,t1.periodunit,t1.periods,t1.bytstatus,COALESCE(to_char(t1.signingtime,'YYYY-MM-DD'),'') signingtimeformat,t1.signingtime,
                COALESCE(to_char(t1.expirationtime,'YYYY-MM-DD'),'') expirationtimeformat,t1.status,t1.expirationtime,
            t1.purpose,t1.certificatestatus,t1.message,t1.signatory as needsignatory,t1.userid,t1.updated_at,t1.created_at,
            t1.orgunitid,t2.contractcode,t1.threeperson,case when t1.status=0 then '草稿' when t1.status=1 then '审核中' else '已审核' end as strstatus,
            COALESCE(t3.name,'') as username,COALESCE(t4.strcustomername,'') as strcustomername,COALESCE(t5.name,'')  as signatoryname,
            COALESCE(t4.representative,'') as representative,t2.code,t2.name,array_to_string(t2.certificate,',') certificate,t2.amount,
            COALESCE(t7.amount,0) as payamount,t2.amount-COALESCE(t7.amount,0) as unpayamount,
             COALESCE(t8.userid,0) as recipient,COALESCE(t8.signatory,0) as signatory,COALESCE(t8.maintainer,0) as maintainer """

        tblname = """ public.project t1 inner join public.projectdetail t2 on t1.id=t2.projectid
            left join public.users t3 on t1.userid=t3.id
            left join public.customers t4 on t1.customerid=t4.id
            left join public.users t5 on t1.signatory=t5.id
            left join public.orgunit t6 on t1.orgunitid=t6.id
            left join (select t2.projectid,t2.projectdetailid,sum(t2.amount) as amount
            from public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
            where t1.type=1 group by t2.projectid,t2.projectdetailid) t7 on t1.id=t7.projectid and t2.id=t7.projectdetailid
             left join public.employee t8 on t2.employeeid=t8.id """
        
        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]

class PaymentUnOutListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        customername = req["customername"]if "customername" in req else ""
        representative = req["representative"]if "representative" in req else ""
        orgunitname = req["orgunitname"]if "orgunitname" in req else ""
        difference = str(req["difference"]) if "difference" in req else ""
        code = req["code"] if "code" in req else ""
        name = req["name"] if "name" in req else ""
        major = req["major"] if "major" in req else ""
        area = req["area"] if "area" in req else ""
        bytstatus= req["bytstatus"] if "bytstatus" in req else ""
        certificate = req["certificate"] if "certificate" in req else ""
        location = req["location"] if "location" in req else ""
        projectdetailid=str(req["projectdetailid"]) if "projectdetailid" in req else "0"
        payinamount =str(req["payinamount"]) if "payinamount" in req else "0"

        orderfield = req["orderfield"] if "orderfield" in req else "code"
        order = req["order"] if "order" in req else 0
        if orderfield=="code" :
            orderfield ="t1.code"
        elif orderfield=="name":
            orderfield ="t1.name"
        elif orderfield=="major":
            orderfield ="t1.major"
        elif orderfield=="area":
            orderfield ="t1.area"
        elif orderfield=="bill_date":
            orderfield ="t1.bill_date"
        else:
            orderfield ="t1.code"        

        strwhere ="t1.org_id=" +str(self.current_org.id)+" and t1.status=2 "
        if code !="" :
            strwhere = strwhere + " and t2.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t2.name like '%"+name+"%'"
        if major !='' :
            strwhere = strwhere + " and  '"+major+"' = ANY(t1.major)"
        if area !='' :
            strwhere = strwhere + " and t1.area like '%"+area+"%'"
        if bytstatus !='' :
            strwhere = strwhere + " and t1.bytstatus like '%"+bytstatus+"%'"
        if certificate !='' :
            strwhere = strwhere + " and '"+certificate+"' = ANY(t2.certificate)"
        if location !='' :
            strwhere = strwhere + " and t2.location like '%"+location+"%'"
        if customername !='' :
            strwhere = strwhere + " and t4.strcustomername like '%"+customername+"%'"
        if orgunitname !='' :
            strwhere = strwhere + " and t6.name like '%"+orgunitname+"%'"
        if representative !='':
            strwhere = strwhere + " and t4.representative like '%"+representative+"%'"
        if difference =="1":
            strwhere = strwhere + " and (t2.signamount-COALESCE(t7.amount,0)>0 or t2.id="""+projectdetailid+""")"""
        if payinamount=="1":
            strwhere = strwhere + " and COALESCE(t9.amount,0)>0 """
        elif payinamount=="2":
            strwhere = strwhere + " and COALESCE(t9.amount,0)=0 """

        strgetfield =""" t2.id,t2.projectid,to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t2.employeeid,"""
        strgetfield =strgetfield +""" t1.code as needcode,t1.name as needname,t1.customerid,t1.neednum,t2.amount as totalamount,
            t1.schooled,array_to_string(t1.certificate,',') needcertificate,t1.location,
            case when t1.socialsecurity=1 then '有' else '无' end needsocialsecuritystr,
            t1.area,t1.periodunit,t1.periods,t1.bytstatus,COALESCE(to_char(t1.signingtime,'YYYY-MM-DD'),'') signingtimeformat,t1.signingtime,
                COALESCE(to_char(t1.expirationtime,'YYYY-MM-DD'),'') expirationtimeformat,t1.status,t1.expirationtime,
            t1.purpose,t1.certificatestatus,t1.message,t1.signatory as needsignatory,t1.userid,t1.updated_at,t1.created_at,
            t1.orgunitid,t2.contractcode,t1.threeperson,case when t1.status=0 then '草稿' when t1.status=1 then '审核中' else '已审核' end as strstatus,
            COALESCE(t3.name,'') as username,COALESCE(t4.strcustomername,'') as strcustomername,COALESCE(t5.name,'')  as signatoryname,
            COALESCE(t4.representative,'') as representative,t2.code,t2.name,array_to_string(t2.certificate,',') certificate,t2.signamount,
            COALESCE(t7.amount,0) as payamount,t2.signamount-COALESCE(t7.amount,0) as unpayamount,
            COALESCE(t8.userid,0) as recipient,COALESCE(t8.signatory,0) as signatory,COALESCE(t8.maintainer,0) as maintainer,
            COALESCE(t9.amount,0) as payinamount """

        tblname = """ public.project t1 inner join public.projectdetail t2 on t1.id=t2.projectid
            left join public.users t3 on t1.userid=t3.id
            left join public.customers t4 on t1.customerid=t4.id
            left join public.users t5 on t1.signatory=t5.id
            left join public.orgunit t6 on t1.orgunitid=t6.id
            left join (select t2.projectid,t2.projectdetailid,sum(t2.amount) as amount
            from public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
            where t1.type=0 group by t2.projectid,t2.projectdetailid) t7 on t1.id=t7.projectid and t2.id=t7.projectdetailid
            left join public.employee t8 on t2.employeeid=t8.id 
            left join (select t2.projectid,t2.projectdetailid,sum(t2.amount) as amount
            from public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
            where t1.type=1 group by t2.projectid,t2.projectdetailid) t9 on t1.id=t9.projectid and t2.id=t9.projectdetailid """
        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]